<?php
/**
 * Object Relational Persistence Mapping Class for table: ticket
 *
 * @package	com.clearhealth
 * @author	ClearHealth Inc.
 */
$loader->requireOnce('ordo/TicketItem.class.php');
$loader->requireOnce('ordo/MedicalRecord.class.php');
$loader->requireOnce('ordo/ExaminationType.class.php');
$loader->requireOnce('ordo/SaleProgram.class.php');
$loader->requireOnce('ordo/Department.class.php');

$loader->requireOnce('includes/Utils.class.php');
$loader->requireOnce('includes/ParacliniqueEngine.class.php');


class Ticket extends ORDataObject {

	/**#@+
	 * Fields of table: ticket mapped to class members
	 */
	var $ticket_id		= '';
	var $department_id	= '';
	var $patient_id		= '';
	var $name			= '';
	var $created		= '';
	var $started 		= '';
	var $closed			= '';
	var $sale_program_store_id		= '';

	var $status		= '';
	var $exam_cause = '';
	var $notes		= '';
	var $enum_examination_type = '';
	var $enum_patient_type = '';
	/**#@-*/

	var $_ticket_prefix = "";
	/**
	 * DB Table
	 */
	var $_table = 'ticket';

	/**
	 * Primary Key
	 */
	var $_key = 'ticket_id';



	/**
	 * Internal Name
	 */
	var $_internalName = 'Ticket';

	const OPEN_STATUS = "open";
	const CLOSED_STATUS = "closed";

	/**
	 * Handle instantiation
	 */
	function Ticket() {
		parent::ORDataObject();
		$conf = Celini::configInstance();

		$this->_ticket_prefix = $conf->get('ticket_prefix');
	}

	function setup($ticketId = 0) {
		if ($ticketId > 0) {
			$this->set('ticket_id', $ticketId);
			$this->populate();
		}
	}

	public function start($ticketId) {
		$sql = "UPDATE ticket SET started = NOW() WHERE ticket_id = $ticketId";
		$this->_execute($sql);

	}

	public function close($ticketId) {
		$sql = "UPDATE ticket SET closed = NOW(), status='" . Ticket::CLOSED_STATUS . "' WHERE ticket_id = $ticketId";
		$this->_execute($sql);
	}

	public function getAllTicketOfPatient($patientId, $open) {
		$status = Ticket::CLOSED_STATUS;
		if ($open) {
			$status = Ticket::OPEN_STATUS;
		}
		$ds = new Datasource_sql();
		$sql = array(
			'cols' 		=> "ticket_id, department_id, patient_id, name, created, status",
			'from' 		=> "$this->_table",
			'where' 	=> "patient_id = $patientId AND status='" . $status ."'",
			'orderby' 	=> 'created DESC'
			);

		$ds->setup($this->_db,$sql);
		return $ds->toArray();
	}

	public function getAllTicketOfPatientByDepartment($patientId, $departmentId, $open) {
		$status = Ticket::CLOSED_STATUS;
		if ($open) {
			$status = Ticket::OPEN_STATUS;
		}
		$ds = new Datasource_sql();
		$sql = array(
			'cols' 		=> "ticket_id, patient_id, name, created, status",
			'from' 		=> "$this->_table",
			'where' 	=> "patient_id = $patientId
				AND department_id = $departmentId
				AND status='" . $status ."'",
			'orderby' 	=> 'created DESC'
			);

		$ds->setup($this->_db,$sql);
		return $ds->toArray();
	}

	public function getDiscountProgramName($ticketId) {
		$saleId = $this->getSaleProgramId($ticketId);
		if (empty($saleId)) {
			return '';
		}
		return Celini::newORDO('SaleProgram', $saleId)->getLocalizedText('name');
	}

	public function getExaminationTypeName($ticketId) {
		$ticket = Celini::newORDO('Ticket', $ticketId);
		$id = $ticket->get('examination_type_id');
		return Celini::newORDO('ExaminationType', $id)->getLocalizedText('name');
	}

	public function getSaleProgramId($ticketId) {
		$ticket = Celini::newORDO('Ticket', $ticketId);
		$saleStoreId = $ticket->get('sale_program_store_id');

		if (empty($saleStoreId)) {
			return '';
		}

		$store = Celini::newORDO('SaleProgramStore', $saleStoreId);
		return $store->get('sale_program_id');
	}

	public function getAllTicketItem($ticketId) {
		$ds = new Datasource_sql();
		$sql = array(
			'cols' 		=> "ticket_item_id, item_type",
			'from' 		=> "ticket_item",
			'where' 	=> "ticket_id=$ticketId",
			'orderby' 	=> "item_order"
			);

		$ds->setup($this->_db, $sql);
		return $ds->toArray('ticket_item_id', 'item_type');
	}

	public function addNewTicket($patientId, $departmentId, $enumPatientType) {
		$patientType = Utils::getEnumKey(EnumerationConstant::PATIENT_TYPE, $enumPatientType);
		$data = array (
				'name'			    => "$this->_ticket_prefix " . date('Y-m-d'),
				'status'		    => Ticket::OPEN_STATUS,
				'patient_id'	    => $patientId,
				'department_id'	    => $departmentId,
		        'enum_patient_type' => $patientType,
		        'created'           => date('Y-m-d H:i:s')
			);
		$this->populateArray($data);
		$this->persist();
	}

	public function delete($ticketId) {
		// Step 1: Delete all ticket_items
		$sql = "select ticket_item_id from ticket_item where ticket_id=$ticketId";
		$res = $this->_execute($sql);
		$ticketItem = Celini::newORDO('TicketItem');
		while ($res && !$res->EOF) {
			$ticketItemId = $res->fields['ticket_item_id'];
			//delete paraclinic assignment.
			ParacliniqueEngine::deleteParaClinicTicketItem($ticketItemId);
			$ticketItem->delete($ticketItemId);
        	$res->MoveNext();
		}

		// Step 2: Delete medical_record
		$sql = "select medical_record_id from medical_record where ticket_id=$ticketId";
		$res = $this->_execute($sql);
		$medicalRecordId = false;
		if ($res && !$res->EOF) {
			$medicalRecordId = $res->fields['medical_record_id'];
		}
		if ($medicalRecordId !== false) {
			Celini::newORDO('MedicalRecord')->delete($medicalRecordId);
		}

		// Step 3: Update simple statistic. If it has not yet started, do not update simple statistic
		$this->setup($ticketId);
		$today = date('Y-m-d');
		if ($this->get('started') != '0000-00-00 00:00:00') {
			$sql = "update simple_statistics set total = total - 1 where date = '" . $today . "'";
			$this->_execute($sql);
		}

		// Last: Delete the ticket
		$sql = "delete from ticket where ticket_id = $ticketId";
		$this->_execute($sql);
	}

	public function updateCommonInfo($ticketId, $insurance,
		$saleProgramStoreId, $examinationTypeId, $examCause=false, $ticketNote=false) {

		$sql = "update $this->_table set
			sale_program_store_id = $saleProgramStoreId,
			insurance = '$insurance',
			examination_type_id= $examinationTypeId";

		if ($examCause !== false) {
			$sql .= ", exam_cause = '$examCause'";
		}
		if ($ticketNote !== false) {
			$sql .= ", notes = '$ticketNote'";
		}
		$sql .= " where ticket_id = $ticketId";
		$this->_execute($sql);

		// Update ticket price
		$this->updateTicketPrice($ticketId);
	}

	public function updateTicketPrice($ticketId) {
		$this->setup($ticketId);
		$info = $this->getTicketInfo();
		$ticketItem = Celini::newORDO('TicketItem');
		$res = $this->getAllTicketItem($ticketId);
		$discountExecuter = new DiscountExecuter($info['sale_program_id']);
		foreach ($res as $ticketItemId => $itemType) {
			$ticketItem->updateTicketItemPrice($ticketItemId, $itemType, $this->insurance, $discountExecuter);
		}
	}

	public function isTicketInProgress($ticketId) {
		$sql = "select * from ticket_item where
			ticket_id = $ticketId and status != '" . TicketItem::OPEN . "'";
		$res = $this->_execute($sql);
		if ($res && !$res->EOF) {
			return true;
		}
		return false;
	}

	public function getFirstTicketItem($ticketId, $status) {
		$sql = "SELECT ticket_item_id
				FROM ticket_item
				WHERE ticket_id = $ticketId AND status='" . $status ."'
				ORDER BY item_order ASC
				LIMIT 1";
		$res = $this->_execute($sql);
		if ($res && !$res->EOF) {
			return $res->fields['ticket_item_id'];
		}
		return false;
	}

	public function getLastTicketItem($ticketId) {
		$sql = "SELECT ticket_item_id
				FROM ticket_item
				WHERE ticket_id = $ticketId
				ORDER BY item_order DESC
				LIMIT 1";
		$res = $this->_execute($sql);
		return $res->fields['ticket_item_id'];
	}

	public function isLastOpenItem($ticketItemId) {
		$ti = Celini::newORDO('TicketItem', $ticketItemId);
		$tiId = $this->getLastTicketItem($ti->get('ticket_id'));

		if ($tiId == $ticketItemId && $ti->get('status') == TicketItem::OPEN) {
			return true;
		}
		return false;
	}

	public function isAnyOpenTicket($patientId) {
		$t = new Ticket();
		$sql = "SELECT ticket_id FROM ticket WHERE patient_id = " . $patientId ." AND status='" . Ticket::OPEN_STATUS ."'";
		$res = $t->_execute($sql);
		if ($res && !$res->EOF) {
			return $res->fields['ticket_id'];
		}
		return false;
	}

	public function isAnyOpenTicketByDepartment($patientId, $departmentId) {
		$t = new Ticket();
		$sql = "SELECT ticket_id
				FROM ticket
				WHERE patient_id = " . $patientId.
					" AND department_id =".$departmentId.
					" AND status='" . Ticket::OPEN_STATUS ."'";
		$res = $t->_execute($sql);
		if ($res && !$res->EOF) {
			return $res->fields['ticket_id'];
		}
		return false;
	}

	public function isExist($ticketId) {
		$res = $this->_execute("select ticket_id from $this->_table where ticket_id = $ticketId");
		if ($res && !$res->EOF) {
			return true;
		}
		return false;
	}

	public function getTicketInfo() {

		$storeId = $this->get('sale_program_store_id');
		$saleProgramId = -1;
		if ($storeId > 0) {
			$store = Celini::newORDO('SaleProgramStore', $storeId);
			$saleProgramId = $store->get('sale_program_id');
		}

		return array(
					'sale_program_id' => $saleProgramId,
					'examination_type_id' => $this->get('examination_type_id'));
	}

	public function updateRealAmount($amount, $ticketId, $modifierId) {
		$sql = "UPDATE ticket
			SET real_payment = $amount,

				modifier_id = $modifierId,
				modified_date = NOW()
			WHERE ticket_id = $ticketId";

		$this->_execute($sql);
	}

	private function isDuplicatedPatient($ticketId, $today) {
		$this->populate($ticketId);
		$patientId = $this->get('patient_id');
		$sql = "SELECT COUNT(*) no FROM $this->_table
			WHERE patient_id = $patientId AND DATE_FORMAT(start, '%Y-%c-%d') = '" . $today . "'";
		$res = $this->_execute($sql);
		if ($res->fields['no'] > 0) {
			return true;
		}
		return false;
	}

	private function getNumberResult($res) {
		if ($res && !$res->EOF){
			return $res->fields['no'];
		}
		return 0;
	}

	public function getDepartmentId() {
		return $this->department_id;
	}

	public function getLastTicketId($patientId) {
		$sql = "SELECT MAX(ticket_id) AS ticket_id
				FROM $this->_table
				WHERE patient_id = $patientId";
		$res = $this->_execute($sql);
		if ($res && !$res->EOF){
			return $res->fields['ticket_id'];
		}
		return false;
	}

	/**
	 * Get the latest ticket id of a specific patient in a specific department
	 * @param $patientId
	 * @param $departmentId
	 * @return ticket id
	 */
	public function getLatestTicketIdByDepartment($patientId, $departmentId){
		$sql = "SELECT MAX(ticket_id) AS ticket_id
				FROM $this->_table
				WHERE patient_id = $patientId AND department_id = $departmentId";
		$res = $this->_execute($sql);
		if ($res && !$res->EOF){
			return $res->fields['ticket_id'];
		}
		return false;
	}

	/**
	 *
	 * @param $patientId
	 * @param $examType
	 *     2 Search for ticket of insurance registration
	 *     1 Search for ticket of service registration
	 * @return unknown_type
	 */
	public function getLatestTicketByDate($patientId, $examType) {
	    $ds = new Datasource_sql();
	    $sql = array(
            'cols'      => "t.ticket_id, DATE(t.created) AS iso_date",
            'from'      => "$this->_table AS t",
	        'where'     => "patient_id = $patientId
	                       AND enum_examination_type = $examType",
            'orderby'   => "t.created DESC",
	        'limit'     => 1
            );

        $ds->setup($this->_db, $sql);
        $res = $ds->toArray();
        if (!empty($res)) {
            return $res[0];
        } else {
            return false;
        }
	}

	public function getEnumPatientType() {
		return $this->enum_patient_type;
	}

    public function getPatientId() {
        return $this->patient_id;
    }

	public function getEnumExaminationType() {
		return $this->enum_examination_type;
	}

	public function getName() {
		return $this->name;
	}

    public function getTicketListByPatient($patientId, $enumPatientType = false, $ticketStatus = false, $examType = false) {
        $format =   L10nHelper::getSqlDateFormat('YmdHi');
        $ds = new Datasource_sql();
        $sql = array (
            'cols'      => "t.name ticket_name, t.ticket_id, t.patient_id, pe.name patient_name, pa.patient_code,
                            t.created, DATE_FORMAT(t.created, '$format') created_formatted, YEAR(pe.date_of_birth) birth_year,
                            (YEAR(CURDATE()) - YEAR(pe.date_of_birth)) AS age,
                            t.enum_patient_type, t.enum_examination_type",
            'from'      => "$this->_table t
                            INNER JOIN person pe ON t.patient_id = pe.person_id
                            INNER JOIN patient pa ON t.patient_id = pa.person_id",
            'orderby'   => "t.ticket_id DESC, t.created DESC"
        );
        if (is_array($patientId)) {
            $ids   =   implode(",", $patientId);
            $sql['where']  =  "t.patient_id IN ($ids)";
        }
        else {
            $sql['where']  =  "t.patient_id = '$patientId'";
        }
        if ($enumPatientType !== false) {
            $sql['where']  .=  " AND t.enum_patient_type='$enumPatientType'";
        }
        if ($ticketStatus !== false) {
            $sql['where']  .=  " AND t.status='$ticketStatus'";
        }

        if ($examType !== false) {
        	$sql['where']	.=	" AND t.enum_examination_type = '$examType'";
        }
        $ds->setup($this->_db, $sql);
        $ticketList =   $ds->toArray();
        $examTypeList      =   Utils::getEnumArrayKey(EnumerationConstant::EXAMINATION_TYPE);
        foreach ($ticketList as &$ticket) {
            $ticket['examination_type']   =   $examTypeList[$ticket['enum_examination_type']];
        }
        return $ticketList;
    }
    
    public function getPatientList($timeRange) {
        
        $orderby = 'p.name ASC';
        $selectTime = "DATE_FORMAT(t.created, '%d/%c/%y %H:%i')";
        
        $today = date('Y-m-d');
        switch ($timeRange) {            
            case 'day':                
                $where = "DATE(t.created) = '$today'";    
                $orderby = 'created DESC';                            
                break;
                
            case 'week':
                $where =  "DATE(t.created) >= SUBDATE('$today', 7)";
                break;
                
            case '2week':
                $where =  "DATE(t.created) >= SUBDATE('$today', 14)";
                break;
                
            case 'month':
                $where = "DATE(t.created) >= SUBDATE('$today', 30)";
                break;
            
            default:
                $where = "DATE(t.created) = '$today'";                
                break;
        }
                
        $ds = new Datasource_sql();
        $sql = array (
            'cols'      => "p.name, p.person_id, pa.patient_code,
                            YEAR(p.date_of_birth) AS birth_year,
                            (YEAR(CURDATE()) - YEAR(p.date_of_birth)) AS age,
                            p.gender, t.ticket_id, $selectTime AS created",
            'from'      => "$this->_table t
                            INNER JOIN person p ON t.patient_id = p.person_id
                            INNER JOIN patient pa ON t.patient_id = pa.person_id",
            'where'     => "$where",
            'orderby'   => "$orderby"            
        );
        
        $ds->setup($this->_db, $sql);
        
        return $ds->toArray();
    }
    
}
?>